package net.suncaper.hotel.mapper.impl;

import net.suncaper.hotel.common.utils.JDBCUtils;
import net.suncaper.hotel.mapper.IRoomMapper;
import net.suncaper.hotel.model.domain.Room;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 房间数据访问实现类
 */
public class RoomMapperImpl implements IRoomMapper {
    private QueryRunner queryRunner = new QueryRunner();

    @Override
    public Room findById(Integer id) {
        String sql = "SELECT * FROM room WHERE id = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanHandler<>(Room.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public Room findByRoomNumber(String roomNumber) {
        String sql = "SELECT * FROM room WHERE room_number = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanHandler<>(Room.class), roomNumber);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Room> findAll() {
        String sql = "SELECT * FROM room ORDER BY room_number";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Room.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Room> findByStatus(String status) {
        String sql = "SELECT * FROM room WHERE status = ? ORDER BY room_number";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Room.class), status);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Room> findByRoomTypeId(Integer roomTypeId) {
        String sql = "SELECT * FROM room WHERE room_type_id = ? ORDER BY room_number";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Room.class), roomTypeId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Room> findByPage(int offset, int pageSize) {
        String sql = "SELECT * FROM room ORDER BY room_number LIMIT ?, ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new BeanListHandler<>(Room.class), offset, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM room";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new ScalarHandler<Long>());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public long countByStatus(String status) {
        String sql = "SELECT COUNT(*) FROM room WHERE status = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new ScalarHandler<Long>(), status);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public long countByRoomTypeId(Integer roomTypeId) {
        String sql = "SELECT COUNT(*) FROM room WHERE room_type_id = ?";
        try {
            return queryRunner.query(JDBCUtils.getConnection(), sql,
                new ScalarHandler<Long>(), roomTypeId);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int insert(Room room) {
        String sql = "INSERT INTO room (room_number, room_type_id, floor, status, notes) " +
                     "VALUES (?, ?, ?, ?, ?)";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql,
                room.getRoomNumber(), room.getRoomTypeId(), room.getFloor(),
                room.getStatus(), room.getNotes());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int update(Room room) {
        String sql = "UPDATE room SET room_number = ?, room_type_id = ?, floor = ?, " +
                     "status = ?, notes = ? WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql,
                room.getRoomNumber(), room.getRoomTypeId(), room.getFloor(),
                room.getStatus(), room.getNotes(), room.getId());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int updateStatus(Integer id, String status) {
        String sql = "UPDATE room SET status = ? WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql, status, id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int delete(Integer id) {
        String sql = "DELETE FROM room WHERE id = ?";
        try {
            return queryRunner.update(JDBCUtils.getConnection(), sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}